package com.pdh.dao.imp;

import com.pdh.bean.User;
import com.pdh.dao.BaseUserDao;
import com.pdh.exception.DuplicatePhoneException;
import com.pdh.exception.UnHandleException;
import com.pdh.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 彭德华
 * @Date 2021-08-29 22:19
 */
public class UserDaoMysql extends DruidUtil implements BaseUserDao {
    //COUNT(ID):总用户 COUNT(TO_DAYS(CREATE_TIME)=TO_DAYS(NOW()) OR NULL):今日新增用户
    public static final String SQL_CONSOLE_USER = "SELECT COUNT(ID) user_count,COUNT(TO_DAYS(CREATE_TIME)=TO_DAYS(NOW()) OR NULL) user_day_reg_count FROM EXPRESS_USER";
    private static final String SQL_FIND_ALL = "SELECT *,INET6_NTOA(LOGIN_IP) IP FROM EXPRESS_USER";
    private static final String SQL_FIND_LIMIT = "SELECT *,INET6_NTOA(LOGIN_IP) IP FROM EXPRESS_USER  LIMIT ?,?";
    private static final String SQL_FIND_BY_USER_PHONE = "SELECT *,INET6_ATON(LOGIN_IP) IP FROM EXPRESS_USER WHERE USER_PHONE = ?";
    private static final String SQL_INSERT = "INSERT INTO EXPRESS_USER(USERNAME,USER_PHONE,PASSWORD,STATUS,USER_ID_CARD_NO,CREATE_TIME) VALUES(?,?,?,0,?,NOW())";
    private static final String SQL_DELETE = "UPDATE EXPRESS_USER SET STATUS=3,UPDATE_TIME=NOW() WHERE ID = ?";
    private static final String SQL_UPDATE = "UPDATE EXPRESS_USER SET USERNAME = ?,USER_PHONE = ?,USER_ID_CARD_NO = ?,PASSWORD = ? WHERE ID = ?";

    //更新数据库中存储的登陆时间 三个预编译参数
    private static final String SQL_LOGIN = "SELECT ID FROM EXPRESS_USER WHERE USER_PHONE=?";
    private static final String SQL_UPDATE_LOGIN_INFO = "UPDATE EXPRESS_USER SET STATUS=1,LOGIN_TIME=NOW(),LOGIN_IP=INET6_ATON(?),IS_COURIER=? WHERE USER_PHONE=?";
    //退出登陆
    private static final String SQL_LOGOUT = "UPDATE EXPRESS_USER SET STATUS=0 WHERE ID=?";


    //把参数全局化，减少创建消耗内存
    Connection conn = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    /**
     * 提供一个把ResultSet转化为User的一个方法
     */
    private User getNewUser(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String username = resultSet.getString("username");
        String userPhone = resultSet.getString("user_phone");
        String password = resultSet.getString("password");
        int stats = resultSet.getInt("status");
        String userIdCardNo = resultSet.getString("user_id_card_no");
        Timestamp loginTime = resultSet.getTimestamp("login_time");
        String loginIp = resultSet.getString("ip");
        boolean isCourier = resultSet.getBoolean("is_courier");
        Timestamp createTime = resultSet.getTimestamp("create_time");
        Timestamp updateTime = resultSet.getTimestamp("update_time");
        return new User(id, username, userPhone, password, stats, userIdCardNo, loginTime, loginIp, isCourier, createTime, updateTime);
    }

    /**
     * 于查询数据库中的用户信息(用户数量，日注册量)
     *
     * @return [{size:总数,day:新增}]
     */
    @Override
    public Map<String, Integer> consoleUser() throws UnHandleException {
        Map<String, Integer> data = new HashMap<>();
        //1.获取数据库的连接
        conn = getConnection();
        //2.预编译sql语句
        try {
            statement = conn.prepareStatement(SQL_CONSOLE_USER);
            //3.填充参数(可选)
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            if (resultSet.next()) {//首先明确得到的是些什么数据，而不是照着教程敲
                int userCount = resultSet.getInt("user_count");
                int userDayRegCount = resultSet.getInt("user_day_reg_count");
                data.put("userCount", userCount);
                data.put("userDayRegCount", userDayRegCount);
            }
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        } finally {
            //6.资源释放
            close(conn, statement, resultSet);
        }
        return data;
    }

    /**
     * @param paging 是否分页标记 true/false 分页/查询所有
     * @param offset sql语句起始索引
     * @param limit  页查询的数量
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean paging, int offset, int limit) throws UnHandleException {
        ArrayList<User> data = new ArrayList<>();
        //1.获取数据库连接
        conn = getConnection();
        //2.编译sql语句
        try {
            if (paging) {
                statement = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数
                statement.setInt(1, offset);
                statement.setInt(2, limit);
            } else {
                statement = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行sql语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()) {
                User user = getNewUser(resultSet);
                data.add(user);
            }
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        } finally {
            //6.释放资源
            close(conn, statement, resultSet);
        }
        return data;
    }

    /**
     * 根据用户手机号查询用户信息
     *
     * @param userPhone 用户手机号
     * @return 查询的用户信息列表，电话不存在时候放回null
     */
    @Override
    public User findByUserPhone(String userPhone) throws UnHandleException {
        //1.获取数据库连接
        conn = getConnection();
        //2.编译sql语句
        try {
            statement = conn.prepareStatement(SQL_FIND_BY_USER_PHONE);
            //3.填充参数
            statement.setString(1, userPhone);
            //4.执行sql
            resultSet = statement.executeQuery();
            //5.获取执行结果
            if (resultSet.next()) {
                User user = getNewUser(resultSet);
                return user;
            }
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        } finally {
            //6.释放资源
            close(conn, statement, resultSet);
        }
        return null;
    }

    /**
     * 用户的增加
     *
     * @param user 录入用户的对象
     * @return
     */
    @Override
    public boolean insert(User user) throws DuplicatePhoneException, UnHandleException {
        //1.获取数据库连接
        conn = getConnection();
        //2.编译sql语句
        try {
            statement = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getUserPhone());
            statement.setString(3, user.getPassword());
            statement.setString(4, user.getUserIdCardNo());
            //4.执行sql语句
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            if (e.getMessage().endsWith("for key 'uk_phone'")) {
                //是因为uk_phone重复 抛出异常到service
                DuplicatePhoneException uk_phone = new DuplicatePhoneException(e.getMessage());
                throw uk_phone;
            } else {
                //抛出未收录的异常，之后还可以编写异常处理
                throw new UnHandleException(e.getMessage());
            }
        } finally {
            //6.释放资源
            close(conn, statement, null);
        }
    }

    /**
     * 用户的修改
     *
     * @param id      要修改的编号
     * @param newUser 新的用户对象
     * @return 修改的结果
     */
    @Override
    public boolean update(int id, User newUser) throws DuplicatePhoneException, UnHandleException {
        conn = getConnection();
        //2.编译sql语句
        try {
            statement = conn.prepareStatement(SQL_UPDATE);
            //3.填充参数
            statement.setString(1, newUser.getUsername());
            statement.setString(2, newUser.getUserPhone());
            statement.setString(3, newUser.getUserIdCardNo());
            statement.setString(4, newUser.getPassword());
            statement.setInt(5, id);
            //4.执行sql
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            if (e.getMessage().endsWith("for key 'uk_phone'")) {
                //是因为uk_phone重复 抛出异常到service
                DuplicatePhoneException uk_phone = new DuplicatePhoneException(e.getMessage());
                throw uk_phone;
            } else {
                //抛出未收录的异常，之后还可以编写异常处理
                throw new UnHandleException(e.getMessage());
            }
        } finally {
            //6.释放资源
            close(conn, statement, null);
        }
    }

    /**
     * 根据id删除单个用户
     *
     * @param id 要删除的用户id
     * @return 删除的结果
     */
    @Override
    public boolean delete(int id) throws UnHandleException {
        //1.获取数据库的连接
        conn = getConnection();
        //2.预编译sql语句
        try {
            statement = conn.prepareStatement(SQL_DELETE);
            //3.填充参数(可选)
            statement.setInt(1, id);
            //4.执行sql语句
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        } finally {
            //6.资源释放
            close(conn, statement, null);
        }
    }

    /**
     * 用户登陆语句
     *
     * @param userPhone
     * @return
     */
    @Override
    public boolean login(String userPhone) throws UnHandleException {
        //1.获取数据库的连接
        conn = getConnection();
        //2.预编译sql语句
        try {
            statement = conn.prepareStatement(SQL_LOGIN);
            //3.填充参数(可选)
            statement.setString(1, userPhone);
            //4.执行sql语句
            resultSet = statement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        } finally {
            //6.资源释放
            close(conn, statement, null);
        }
    }

    /**
     * 更新用户登陆信息
     * @param userPhone
     * @param ip
     * @return
     */
    @Override
    public boolean updateLoginInformation(String userPhone, boolean isCourier, String ip) throws UnHandleException {
        //1.获取连接
        conn = getConnection();
        //2.预编译sql语句
        try {//try/catch更有利于释放资源
            statement = conn.prepareStatement(SQL_UPDATE_LOGIN_INFO);//体验命名规范
            //3.填充参数
            statement.setString(1,ip);
            statement.setBoolean(2,isCourier);//0未登录 1已登陆
            statement.setString(3,userPhone);
            //4.执行
            int row = statement.executeUpdate();//不需要获取结果 学框架以后可以更多的去关注这些东西使得项目数据更加稳健
            return row>0?true:false;
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        }finally {
            //5.释放资源
            close(conn,statement,null);
        }
    }

    /**
     * 退出登陆，修改状态码
     * @param id
     * @return
     */
    @Override
    public boolean logout(int id) throws UnHandleException {
        //1.获取连接
        conn = getConnection();
        //2.预编译sql语句
        try {//try/catch更有利于释放资源
            statement = conn.prepareStatement(SQL_LOGOUT);//体验命名规范
            //3.填充参数
            statement.setInt(1,id);
            //4.执行
            int row = statement.executeUpdate();//不需要获取结果 学框架以后可以更多的去关注这些东西使得项目数据更加稳健
            return row>0?true:false;
        } catch (SQLException e) {
            //抛出未收录的异常，之后还可以编写异常处理
            throw new UnHandleException(e.getMessage());
        }finally {
            //5.释放资源
            close(conn,statement,null);
        }
    }


}

